const { queryDatabase, insertDatabase, updateDatabase, deleteDatabase, executeTransaction } = require('../queue-client');
// 移除了不存在的缓存管理器引用

// 查询所有货位
async function getAllSlots() {
  try {
    const sql = `
      SELECT s.*, 
             g.name as goods_name,
             g.quantity as goods_quantity,
             g.weight as goods_weight,
             g.entry_date as goods_entry_date
      FROM storage_slots s
      LEFT JOIN goods_info g ON s.id = g.slot_id
      ORDER BY s.layer, s.row, s.column
    `;
    
    const rows = await queryDatabase(sql);
    console.log('getAllSlots返回的数据:', rows);
    
    return Array.isArray(rows) ? rows : [];
  } catch (error) {
    console.error('查询货位数据失败:', error);
    throw error;
  }
}

// 查询单个货位
async function getSlotById(slotId) {
  try {
    const sql = `
      SELECT s.*, 
             g.name as goods_name,
             g.quantity as goods_quantity,
             g.weight as goods_weight,
             g.entry_date as goods_entry_date
      FROM storage_slots s
      LEFT JOIN goods_info g ON s.id = g.slot_id
      WHERE s.id = ?
    `;
    
    const rows = await queryDatabase(sql, [slotId]);
    const result = rows.length > 0 ? rows[0] : null;
    
    return result;
  } catch (error) {
    console.error('查询货位详情失败:', error);
    throw error;
  }
}

// 批量插入货位数据
async function bulkInsertSlots(slots) {
  try {
    // 使用事务插入数据
    const operations = [];
    
    // 先删除所有数据
    operations.push({
      type: 'DELETE',
      sql: 'DELETE FROM goods_info',
      params: []
    });
    
    operations.push({
      type: 'DELETE',
      sql: 'DELETE FROM storage_slots',
      params: []
    });
    
    // 然后插入新数据
    for (const slot of slots) {
      operations.push({
        type: 'INSERT',
        sql: `
          INSERT INTO storage_slots (id, layer, row, column, has_goods, created_at, updated_at)
          VALUES (?, ?, ?, ?, ?, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)
        `,
        params: [
          slot.id,
          slot.layer,
          slot.row,
          slot.column,
          slot.hasGoods ? 1 : 0
        ]
      });
    }
    
    await executeTransaction(operations);
    console.log(`✅ 批量插入 ${slots.length} 个货位`);
    
  } catch (error) {
    console.error('批量插入货位失败:', error);
    throw error;
  }
}

// 更新货位状态
async function updateSlot(slotId, hasGoods, goodsInfo = null) {
  try {
    // 使用事务更新数据
    const operations = [];
    
    // 更新货位状态
    operations.push({
      type: 'UPDATE',
      sql: `
        UPDATE storage_slots 
        SET has_goods = ?, updated_at = CURRENT_TIMESTAMP
        WHERE id = ?
      `,
      params: [hasGoods ? 1 : 0, slotId]
    });
    
    // 删除旧的货物信息
    operations.push({
      type: 'DELETE',
      sql: 'DELETE FROM goods_info WHERE slot_id = ?',
      params: [slotId]
    });
    
    // 如果有货物，插入新的货物信息
    if (hasGoods && goodsInfo) {
      operations.push({
        type: 'INSERT',
        sql: `
          INSERT INTO goods_info (slot_id, name, quantity, weight, entry_date, updated_at)
          VALUES (?, ?, ?, ?, ?, CURRENT_TIMESTAMP)
        `,
        params: [
          slotId,
          goodsInfo.name,
          goodsInfo.quantity,
          goodsInfo.weight,
          goodsInfo.entryDate
        ]
      });
    }
    
    await executeTransaction(operations);
    console.log(`✅ 更新货位 ${slotId}`);
    
  } catch (error) {
    console.error('更新货位失败:', error);
    throw error;
  }
}

// 删除货位记录
async function deleteSlot(slotId) {
  try {
    const sql = 'DELETE FROM storage_slots WHERE id = ?';
    await deleteDatabase(sql, [slotId]);
    console.log(`✅ 删除货位记录，ID: ${slotId}`);
    
  } catch (error) {
    console.error('删除货位记录失败:', error);
    throw error;
  }
}

// 获取无货货位（不考虑任务状态）
async function getEmptySlots() {
  try {
    console.log('开始执行无货货位查询');
    
    const sql = `
      SELECT * 
      FROM storage_slots 
      WHERE has_goods = 0
      ORDER BY row, column, layer
    `;
    
    const rows = await queryDatabase(sql);
    console.log(`查询到 ${rows.length} 个无货货位`);
    return rows;
  } catch (error) {
    console.error('查询无货货位失败:', error);
    throw error;
  }
}

module.exports = {
  getAllSlots,
  getSlotById,
  bulkInsertSlots,
  updateSlot,
  deleteSlot,
  getEmptySlots
};